package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.CorpCommpany;
import com.pagepub.expo.entity.IdName;

@Repository
public class CorpCommpanyDao  {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	
	private static String tableName="Corp_Commpany";
	
	private static String ID_NAME="SELECT id, corpName name FROM "+tableName +" order by name";

	private static String QUERY_ALL = "SELECT id,siteTitle,	siteUrl, clickNum,sequence," +
			"catalogId, addTime, updateTime,content,corpName," +
			"address,zipCode,telephone,fax,email," +
			"qq,mobile,description,video,icpNum," +
			"corpLogo, isValid,userName,password,linkMan," +
			"domain,templateId,isCommend, coreOrder,corpTypeId,"+
			"pavilionArea,pavilionCode,isSpecial,pavilionCount,corpNameEn,isAuditing FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
	 		"id,siteTitle,siteUrl,clickNum,sequence,"+
			"catalogId, addTime, updateTime,content,corpName," +
			"address,zipCode,telephone,fax,email," +
			"qq,mobile,description,video,icpNum," +
			"corpLogo, isValid,userName,password,linkMan," +
			"domain,templateId,isCommend, coreOrder,corpTypeId," +
			"pavilionArea,pavilionCode,isSpecial,pavilionCount,corpNameEn,isAuditing)VALUES(" +
			":id,:siteTitle,:siteUrl,:clickNum,:sequence,"+
			":catalogId,:addTime,:updateTime,:content,:corpName," +
			":address,:zipCode,:telephone,:fax,:email," +
			":qq,:mobile,:description,:video,:icpNum," +
			":corpLogo,:isValid,:userName,:password,:linkMan," +
			":domain,:templateId,:isCommend,:coreOrder,:corpTypeId," +
			":pavilionArea,:pavilionCode,:isSpecial,:pavilionCount,:corpNameEn,:isAuditing)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE ID=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName + " SET " +
			"updateTime=:updateTime,content=:content,corpName=:corpName," +
			"address=:address,zipCode=:zipCode,telephone=:telephone,fax=:fax,email=:email," +
			"qq=:qq,mobile=:mobile,description=:description,video=:video,icpNum=:icpNum," +
			"corpLogo=:corpLogo,isValid=:isValid,userName=:userName,password=:password,linkMan=:linkMan," +
			"domain=:domain,templateId=:templateId,isCommend=:isCommend,coreOrder=:coreOrder,corpTypeId=:corpTypeId," +
			"pavilionArea=:pavilionArea,pavilionCode=:pavilionCode,isSpecial=:isSpecial,pavilionCount=:pavilionCount,corpNameEn=:corpNameEn,isAuditing=:isAuditing"+
			"WHERE id=:id";

	private final ParameterizedRowMapper<CorpCommpany> mapper = new ParameterizedRowMapper<CorpCommpany>() {
		public CorpCommpany mapRow(ResultSet rs, int rowNum) throws SQLException {
			CorpCommpany obj = new CorpCommpany();
			obj.setId(rs.getLong("id"));
			obj.setSiteTitle(rs.getString("siteTitle"));
			obj.setSiteUrl(rs.getString("siteUrl"));
			obj.setClickNum(rs.getInt("clickNum"));
			obj.setSequence(rs.getInt("sequence"));
			obj.setCatalogId(rs.getString("catalogId"));
			obj.setAddTime(rs.getTimestamp("addTime"));
			obj.setUpdateTime(rs.getTimestamp("updateTime"));
			obj.setContent(rs.getString("content"));
			obj.setCorpName(rs.getString("corpName"));
			obj.setAddress(rs.getString("address"));
			obj.setZipCode(rs.getString("zipCode"));
			obj.setTelephone(rs.getString("telephone"));
			obj.setFax(rs.getString("fax"));
			obj.setEmail(rs.getString("email"));
			obj.setQq(rs.getString("qq"));
			obj.setMobile(rs.getString("mobile"));
			obj.setDescription(rs.getString("description"));
			obj.setVideo(rs.getString("video"));
			obj.setIcpNum(rs.getString("icpNum"));
			obj.setCorpLogo(rs.getString("corpLogo"));
			obj.setIsValid(rs.getShort("isValid"));
			obj.setUserName(rs.getString("userName"));
			obj.setPassword(rs.getString("password"));
			obj.setLinkMan(rs.getString("linkMan"));
			obj.setDomain(rs.getString("domain"));
			obj.setTemplateId(rs.getString("templateId"));
			obj.setIsCommend(rs.getByte("isCommend"));
			obj.setCoreOrder(rs.getInt("coreOrder"));	
			obj.setCorpTypeId(rs.getInt("corpTypeId"));
			//pavilionArea=:pavilionArea,pavilionCode=:pavilionCode,isSpecial=:isSpecial,pavilionCount=:pavilionCount,corpNameEn=:corpNameEn
			obj.setPavilionArea(rs.getString("pavilionArea"));
			obj.setPavilionCode(rs.getString("pavilionCode"));
			obj.setPavilionCount(rs.getInt("pavilionCount"));
			obj.setIsSpecial(rs.getInt("isSpecial"));
			obj.setCorpNameEn(rs.getString("corpNameEn"));
			obj.setIsAuditing(rs.getInt("isAuditing"));
			return obj;
		}
	};

	public List<CorpCommpany> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public CorpCommpany findById(Long id) {
		List<CorpCommpany> list = template.query(QUERY_ALL + " WHERE id=?",
				new Object[] { id }, mapper);
		if (list.size() == 0) {
			return null;
		} else
			return list.get(0);
	}
	

	
	public CorpCommpany findByUserName(String userName) {
		List<CorpCommpany> corpCommpanys = template.query(QUERY_ALL + " WHERE isValid=? and userName=?",
				new Object[] {1, userName }, mapper);
		if (corpCommpanys.size() == 0) {
			return null;
		} else
			return corpCommpanys.get(0);
	}

	
	public void insert(CorpCommpany bean) {
		if(bean.getId()==null){
			bean.setId(new Long(appSeqDao.getSequence("corp_commpany_seq")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(CorpCommpany bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, mapper);
		return lists;
	}
	public List<IdName> findIdNames(){
		List lists = template.query(ID_NAME, IdName.idNameMapper);
		return lists;
	}
	
	public void audit(Long id, boolean pass){
		int aduiting=0;
		if(pass==true){
			aduiting=1;
		}
		template.update("update " + tableName+" set isAuditing=? where id=?", new Object[]{aduiting,id});
	}
}
